% cohort method to statistics on year data
clear all;
clc;
freqj=4;% 4 quarters
year_list=1998:2010;

year_list_text=num2str(year_list);
rateList= 1.5:0.5:4.5;
DEFAULT = -2;
NOTSET = -1;

rateList=[rateList, DEFAULT];

DURATION = 4;
NUMBER_OF_YEAR = length(year_list) - DURATION;
RATE_LIST_LENGTH = length(rateList);
%db1_open = database('TP06_Y_RJ_fin_R5_G1000k_MR7','', ''); %open the connection to the database
db1_open = database('TP06_Y_RJ_fin_R5_G250_1000k_MR7','', ''); 



pathCount = zeros(NUMBER_OF_YEAR, RATE_LIST_LENGTH -1, RATE_LIST_LENGTH -1, RATE_LIST_LENGTH -1, RATE_LIST_LENGTH -1, RATE_LIST_LENGTH);
for yearIdx=1:NUMBER_OF_YEAR % i=1:NUMBER_OF_YEAR number of year
    
    year1 = num2str(year_list(yearIdx));
    year2 = num2str(year_list(yearIdx+DURATION));
    year_lookfor=year_list(yearIdx:yearIdx+DURATION);
    year_lookfor_length = length(year_lookfor);

    %get data for 5 years
    sqlquery1 = strcat('SELECT Year, [Customer Number] as custumerId, [Overall Risk Rating (Hist)] as riskRating, status, default FROM TP06_Y_RJ_fin_R5_G250_1000k_MR7 where TP06_Y_RJ_fin_R5_G250_1000k_MR7.year BETWEEN ''',year1,''' and ''',year2,'''');
    setdbprefs('DataReturnFormat','structure')
    results = fetch(db1_open, sqlquery1);
    
    val = size(results.Year);
    
    custumersMap = containers.Map();
    
    for i =1:val
        
        custumerIdStr = results.custumerId{i};
        riskRating = results.riskRating(i);
        year = str2double(results.Year(i));
        
        
        default = strcmp('Default', results.default(i));
        unhealthy = strcmp('Unhealthy', results.status(i));
        
        if isKey(custumersMap, custumerIdStr) == 0
            %the custumer is not in the map, so create it
            
            %create structure
            myStruct.custumerId = custumerIdStr;
            myStruct.rates = -1 * ones (1, year_lookfor_length); %myStruct.rates = [-1, -1, -1];
            
        else
            myStruct = custumersMap(custumerIdStr);
        end
        % put the right rating
        myStruct = fillRate(myStruct, year_lookfor, year, riskRating, default, unhealthy);
        
        % insert data in the map
        custumersMap(custumerIdStr) = myStruct;
    end
    
    
    
    
    custumersStructures = custumersMap.values;
    custumersStructuresLength = length(custumersStructures);
    
    
    % -1 == disapear
    % -2 == default
    % 0  == i don't know
    for i = 1:custumersStructuresLength
        myStruct = custumersStructures{i};
        
        for j = 1: year_lookfor_length
            rate = myStruct.rates(j);
            
            if rate == NOTSET || rate == 0
                remove(custumersMap, myStruct.custumerId);
                break;
            elseif (rate == DEFAULT) && j ~= year_lookfor_length
                remove(custumersMap, myStruct.custumerId);
                break;
            end
        end
    end
    
    
    custumersStructures = custumersMap.values;
    custumersStructuresLength = length(custumersStructures);
    
    year_lookfor_length = length(year_lookfor);
    
    
    for i = 1 : custumersStructuresLength
        myStruct = custumersStructures{i};
        
        
        position = zeros(1,year_lookfor_length);
        for j = 1: year_lookfor_length
            rate = myStruct.rates(j);
            p = find(rateList == rate);
            position(j) = p;
        end
        
        %get the count for one path
        pCount = pathCount(yearIdx, position(1),position(2),position(3),position(4),position(5));
        
        %increment the count + 1 for that path
        pathCount(yearIdx, position(1),position(2),position(3),position(4), position(5)) = pCount + 1;
    end
    disp( strcat(['Year: ' year1 ' to ' year2]));
end


close (db1_open);
%To calculate the average counts and prob

PathCountTotal=sum(pathCount(1:NUMBER_OF_YEAR,:,:,:,:,:));
PathCountAvg=PathCountTotal./NUMBER_OF_YEAR;
PathCountAvgProb=zeros(1,RATE_LIST_LENGTH-1,RATE_LIST_LENGTH-1,RATE_LIST_LENGTH-1,RATE_LIST_LENGTH-1,RATE_LIST_LENGTH);
for i=1:RATE_LIST_LENGTH-1    
    for j=1:RATE_LIST_LENGTH-1
        for k=1:RATE_LIST_LENGTH-1
            for d= 1:RATE_LIST_LENGTH-1
                for t = 1:RATE_LIST_LENGTH
            PathCountAvgProb(1,i,j,k,d,t)=PathCountAvg(1,i,j,k,d,t)./sum(PathCountAvg(1,i,j,k,d,1:RATE_LIST_LENGTH));
                end
            end
        end
    end
end


